package com.yzy.soft.system.freemarker;

import com.yzy.soft.common.persistence.dao.MetadataColumnMapper;
import com.yzy.soft.common.persistence.dao.MetadataDatabaseMapper;
import com.yzy.soft.common.persistence.dao.MetadataTableMapper;
import com.yzy.soft.common.persistence.model.MetadataColumn;
import com.yzy.soft.common.persistence.model.MetadataDatabase;
import com.yzy.soft.common.persistence.model.MetadataTable;

import com.yzy.soft.system.freemarker.database.Column;
import com.yzy.soft.system.freemarker.database.Table;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Component
public class SynchronousMetadata {
    private DBInfo dbInfo;
    @Autowired
    private MetadataTableMapper metadataTableMapper;
    @Autowired
    private MetadataColumnMapper metadataColumnMapper;

    @Autowired
    private MetadataDatabaseMapper metadataDatabaseMapper;


    public DBInfo getDbInfo() {
        dbInfo = new DBInfo();
        dbInfo.setDriver("com.mysql.jdbc.Driver");
        dbInfo.setUrl("jdbc:mysql://127.0.0.1:3307/codebuilder?characterEncoding=utf8&useSSL=false");
        dbInfo.setUsername("root");
        dbInfo.setPassword("root");
        return dbInfo;
    }

    public DBInfo getDbInfo(MetadataDatabase metadataDatabase) {
        dbInfo = new DBInfo();
        dbInfo.setDriver("com.mysql.jdbc.Driver");
        dbInfo.setUrl(metadataDatabase.getUrl());
        dbInfo.setUsername(metadataDatabase.getUsername());
        dbInfo.setPassword(metadataDatabase.getPassword());
        return dbInfo;
    }

    /**
     * 数据库测试连接
     * @param metadataDatabase
     * @return
     */
    public boolean connectTest(MetadataDatabase metadataDatabase) {
        dbInfo = this.getDbInfo(metadataDatabase);
        JdbcUtil jdbcUtil = new JdbcUtil(dbInfo.getDriver(), dbInfo.getUrl(), dbInfo.getUsername(), dbInfo.getPassword());

        Connection connection = jdbcUtil.getConnection();
        if (null != connection) {
            return true;
        }

        return false;
    }


    public void synchronousMetadataDatabase(Integer id) {
        List<MetadataTable> metadataTables = this.synchronousMetadataDatabaseSource(id);
        this.synchronousMetadataDatabaseTarget(metadataTables, id);
    }

    public void synchronousMetadataDatabase() {
        List<MetadataDatabase> metadataDatabases = metadataDatabaseMapper.selectAllDatabase();
        for (int i = 0; i <metadataDatabases.size() ; i++) {
            MetadataDatabase metadataDatabase = metadataDatabases.get(i);
            this.synchronousMetadataDatabase(metadataDatabase.getId());
        }
    }

    public List<MetadataTable> synchronousMetadataDatabaseSource(Integer id) {
        MetadataDatabase metadataDatabase = metadataDatabaseMapper.selectById(id);
        this.getDbInfo(metadataDatabase);
        List<MetadataTable> metadataTables = new ArrayList<>();
        MetadataTable metadataTable = null;
        this.getDbInfo();
        JdbcUtil jdbcUtil = new JdbcUtil(dbInfo.getDriver(), dbInfo.getUrl(), dbInfo.getUsername(), dbInfo.getPassword());
        List<Table> tables = jdbcUtil.getTablesList();
        Date date = new Date();
        for (int i = 0; i < tables.size(); i++) {
            metadataTable = new MetadataTable();
            Table table = tables.get(i);
            metadataTable.setName(table.getTableName());
            metadataTable.setCreateTime(date);
            metadataTable.setUpdateTime(date);
            metadataTable.setCheckTime(date);
            metadataTable.setComment(table.getRemarks());
            metadataTable.setCreatetime(date);
            metadataTable.setLastupdatetime(date);
            metadataTable.setDatabaseId(id);
            List<MetadataColumn> metadataColumns = new ArrayList<>();
            MetadataColumn metadataColumn = null;
            List<Column> columnsInfo = jdbcUtil.getColumnsInfoByTable(metadataTable.getName());
            for (int j = 0; j < columnsInfo.size(); j++) {
                metadataColumn = new MetadataColumn();
                Column column = columnsInfo.get(j);
                metadataColumn.setField(column.getColumnName());
                metadataColumn.setLength(String.valueOf(column.getColumnSize()));
                metadataColumn.setType(column.getDataTypeName());
                metadataColumn.setComment(column.getRemarks());
                metadataColumn.setTableId(metadataTable.getId());
                metadataColumn.setDatabaseId(id);
                metadataColumns.add(metadataColumn);
            }
            metadataTable.setColumns(metadataColumns);
            metadataTables.add(metadataTable);
        }
        return metadataTables;
    }

    public void synchronousMetadataDatabaseTarget(List<MetadataTable> metadataTables, Integer id) {
        metadataTableMapper.deleteByDatabaseId(id);
        metadataColumnMapper.deleteByDatabaseId(id);
        for (int i = 0; i < metadataTables.size(); i++) {
            MetadataTable metadataTable = metadataTables.get(i);
            metadataTableMapper.insert(metadataTables.get(i));
            List<MetadataColumn> metadataColumns = metadataTable.getColumns();
            for (int j = 0; j < metadataColumns.size(); j++) {
                MetadataColumn metadataColumn = metadataColumns.get(j);
                metadataColumn.setTableId(metadataTable.getId());
                metadataColumnMapper.insert(metadataColumn);
            }
        }
    }

    @Transactional
    public void synchronousMetadataTable(Integer tableId) throws Exception {
        metadataColumnMapper.deleteByTableId(tableId);
        MetadataTable metadataTable = metadataTableMapper.selectById(tableId);
        MetadataDatabase metadataDatabase = metadataDatabaseMapper.selectById(metadataTable.getDatabaseId());
        DBInfo dbInfo = this.getDbInfo(metadataDatabase);
        if (null == dbInfo) {
            throw new Exception("数据库连接失败");
        }
        List<MetadataColumn> metadataColumns = new ArrayList<>();
        MetadataColumn metadataColumn = null;
        JdbcUtil jdbcUtil = new JdbcUtil(this.dbInfo.getDriver(), this.dbInfo.getUrl(), this.dbInfo.getUsername(), this.dbInfo.getPassword());
        List<Column> columnsInfo = jdbcUtil.getColumnsInfoByTable(metadataTable.getName());
        for (int j = 0; j < columnsInfo.size(); j++) {
            metadataColumn=new MetadataColumn();
            Column column = columnsInfo.get(j);
            metadataColumn.setField(column.getColumnName());
            metadataColumn.setLength(String.valueOf(column.getColumnSize()));
            metadataColumn.setType(column.getDataTypeName());
            metadataColumn.setComment(column.getRemarks());
            metadataColumn.setTableId(metadataTable.getId());
            metadataColumns.add(metadataColumn);
        }
        if (metadataColumns.size() == 0) {
            throw new Exception("没有查询到列");
        }
        for (int i = 0; i < metadataColumns.size(); i++) {
            metadataColumnMapper.insert(metadataColumns.get(i));
        }
    }
//        List<HtmlTable> tables = jdbcUtil.getTablesList();
//        Date date = new Date();
//        for (int i = 0; i < tables.size(); i++) {
//            metadataTable = new MetadataTable();
//            HtmlTable table = tables.get(i);
//            metadataTable.setName(table.getTableName());
//            metadataTable.setCreateTime(date);
//            metadataTable.setUpdateTime(date);
//            metadataTable.setCheckTime(date);
//            metadataTable.setComment(table.getRemarks());
//            metadataTable.setCreatetime(date);
//            metadataTable.setLastupdatetime(date);
//            metadataTableMapper.insert(metadataTable);
//            metadataTables.add(metadataTable);
//        }
//        return metadataTables;
//    }


    public List<MetadataTable>  synchronousMetadataTable() throws Exception {
        return null;
    }


    public void synchronousMetadataColum() throws Exception{
        this.getDbInfo();
        JdbcUtil jdbcUtil = new JdbcUtil(dbInfo.getDriver(), dbInfo.getUrl(), dbInfo.getUsername(), dbInfo.getPassword());
//        HtmlTable table = new HtmlTable();
        List<MetadataColumn> metadataColumns = new ArrayList<>();
        MetadataColumn metadataColumn = null;
        List<MetadataTable> metadataTables = this.synchronousMetadataTable();
        for (int i = 0; i < metadataTables.size(); i++) {
            MetadataTable metadataTable = metadataTables.get(i);
            metadataColumn = new MetadataColumn();
            List<Column> columnsInfo = jdbcUtil.getColumnsInfoByTable(metadataTable.getName());

            for (int j = 0; j < columnsInfo.size(); j++) {
                Column column = columnsInfo.get(j);
                metadataColumn.setField(column.getColumnName());
                metadataColumn.setLength(String.valueOf(column.getColumnSize()));
                metadataColumn.setType(column.getDataTypeName());
                metadataColumn.setComment(column.getRemarks());
                metadataColumn.setTableId(metadataTable.getId());
                metadataColumnMapper.insert(metadataColumn);
            }
        }


    }

    public void synchronousMetadataColum(List<Integer> tableIds) throws Exception{
        for (int i = 0; i < tableIds.size(); i++) {
            this.synchronousMetadataColum(tableIds.get(i));
        }
    }


    public void synchronousMetadataColum(Integer tableId) throws Exception {
        metadataColumnMapper.deleteByTableId(tableId);
        this.getDbInfo();
        JdbcUtil jdbcUtil = new JdbcUtil(dbInfo.getDriver(), dbInfo.getUrl(), dbInfo.getUsername(), dbInfo.getPassword());
        MetadataColumn metadataColumn = null;
        List<MetadataTable> metadataTables = this.synchronousMetadataTable();
        for (int i = 0; i < metadataTables.size(); i++) {
            MetadataTable metadataTable = metadataTables.get(i);
            metadataColumn = new MetadataColumn();
            List<Column> columnsInfo = jdbcUtil.getColumnsInfoByTable(metadataTable.getName());
            for (int j = 0; j < columnsInfo.size(); j++) {
                Column column = columnsInfo.get(j);
                metadataColumn.setField(column.getColumnName());
                metadataColumn.setLength(String.valueOf(column.getColumnSize()));
                metadataColumn.setType(column.getDataTypeName());
                metadataColumn.setComment(column.getRemarks());
                metadataColumn.setTableId(metadataTable.getId());
                metadataColumnMapper.insert(metadataColumn);
            }
        }
    }
}
